﻿CREATE PROCEDURE [dbo].[proc_Order_Getlist_AnalyseFinish_In]
(
	@InCompanyId varchar(250),
	@StateId int,
	@TypeId int,
	@WayId int,
	@ProcessId int,
	@Stext nvarchar(50),
	@StartDate nvarchar(10),
	@EndDate nvarchar(10),
	@StartIndex int,
	@EndIndex int,
	@NOE_Flag int
)
AS
begin
Declare @cSql nvarchar(4000),@cWhere1 nvarchar(500)

Set @cWhere1 = 'CompanyId In('+@InCompanyId+') And CreateDate>=@sRq And CreateDate<=@eRq'
Set @cSql = 'CompanyId In('+@InCompanyId+')'

If @StateId<>0
	Set @cSql = @cSql + ' And StateId='+Convert(varchar(10),@StateId)
Else
	Set @cSql = @cSql + ' And StateId>2'

If @TypeId<>0
	Set @cSql = @cSql + ' And TypeId='+Convert(varchar(10),@TypeId)

If @WayId<>0
	Set @cSql = @cSql + ' And WayId='+Convert(varchar(10),@WayId)

If @ProcessId<>0
	Set @cSql = @cSql + ' And ProcessId='+Convert(varchar(10),@ProcessId)

If @Stext<>''
Begin
	Set @cSql = @cSql + ' And (CusName like ''%'+@Stext+'%'' Or Salesman like ''%'+@Stext+'%'')'
	Set @cWhere1 = @cWhere1 + ' And (CusName like ''%'+@Stext+'%'' Or Salesman like ''%'+@Stext+'%'')'
End

If @NOE_Flag<>100
Begin
	Set @cSql = @cSql + ' And NOE_Flag=' + Convert(varchar(10),@NOE_Flag)
	Set @cWhere1 = @cWhere1 + ' And NOE_Flag=' + Convert(varchar(10),@NOE_Flag)
End
--By Zht 2014-05-09 去掉非企限制，对项目分析人员可见
--Else
--Begin
--	Set @cSql = @cSql + ' And NOE_Flag<>1'
--	Set @cWhere1 = @cWhere1 + ' And NOE_Flag<>1'
--End

Set @cSql='Declare @sRq Datetime,@eRq Datetime
Set @sRq = Convert(Datetime,''' + @StartDate + ' 00:00:00'')
Set @eRq = Convert(Datetime,''' + @EndDate + ' 23:59:59'')

;WITH List1 As (
	Select Top 1000 OrderId As OId,Min(CreateDate) As AnalyseDate
	From Project
	Where ' + @cWhere1 + '
	Group By OrderId
	Order By AnalyseDate Desc
),
List2 As (
	Select ROW_NUMBER() OVER (Order By List1.AnalyseDate Desc) As Row
		,Id,o.OId
		,IsNull(OrderId,'''') As OrderId
		,CusId
		,CusName
		,CusPerson
		,CusPersonTel
		,CusDescription
		,CusEstimate
		,TypeId
		,TypeName
		,WayId
		,WayName
		,ProcessId
		,ProcessName
		,Salesman
		,SalesmanAccount
		,TradeDate
		,CreateDate
		,IsNull(YingShouPrice,0) as YingShouPrice
		,IsNull(ShiShouPrice,0) as ShiShouPrice
		,Discount
		,PaymentTypeId
		,PaymentType
		,BankId
		,Bank
		,PaymentDescription
		,OtherDescription
		,DepId
		,CompanyId
		,StateId
		,IsGreen
		,NOE_Flag
		,List1.AnalyseDate
		--,Isnull((select Pizhu from Customer_PiZhu where CustomerId=CusId),'''') as Pizhu --批注内容字段 2015.08.20 
		--,Isnull((select CusState from Customer_PiZhu where CustomerId=CusId),0) as CusState --是否有批注字段 
	From [Order] o Right Join List1 On o.OId=List1.OId
	Where ' + @cSql + '
)

Select *,(Select Count(0) From List2) As RecordCount,
,IsNull((Select Title From Order_State Where Id=List2.StateId),''...'') As StateName
From List2
Where Row>='+Convert(varchar(10),@StartIndex)+' And Row <='+Convert(varchar(10),@EndIndex)+'
Order By Row'

	Exec(@cSql)
	--Print (@cSql)
	--return (@cSql)
End
